import logging
from typing import Optional

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_layer_router = APIRouter(prefix="/data/tsLayer", tags=["楼层管理"])


class LayerSearch(PaginationBase):
    flayerName: str = Field(None, description="楼层名称")
    fpointNo: str = Field(None, description="所属楼编号")
    fsiteNo: str = Field(None, description="所属站点")


# todo 3.楼层信息
@data_layer_router.get("/list", summary="楼层查询")
def list_data_layer(layer: LayerSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_layer where 1=1"
    if layer.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = layer.fsiteNo
    if layer.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = layer.fpointNo
    if layer.flayerName is not None:
        sql = sql + " and flayer_name like concat('%', :flayerName ,'%')"
        param["flayerName"] = layer.flayerName
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select flayer_no,flayer_name,fsite_no,fpoint_no,fpoint_l,fpoint_d,fpoint_h,"
    sql = sql + " fpoint_w,fimage,fvideo_url,fperson,flayer_state,fupddate,foperator"
    sql = sql + " from a_ts_layer where 1=1"
    if layer.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = layer.fsiteNo
    if layer.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = layer.fpointNo
    if layer.flayerName is not None:
        sql = sql + " and flayer_name like concat('%', :flayerName ,'%')"
        param["flayerName"] = layer.flayerName
    if layer.order == "desc":
        sql = sql + " order by  flayer_no desc "
    else:
        sql = sql + " order by  flayer_no "
    sql = sql + " limit :limit offset :offset"
    param["offset"] = layer.offset
    param["limit"] = layer.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['flayerNo'] = data[key][0]
        d1['flayerName'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fpointNo'] = data[key][3]
        d1['fpointL'] = data[key][4]
        d1['fpointD'] = data[key][5]
        d1['fpointH'] = data[key][6]
        d1['fpointW'] = data[key][7]
        d1['fimage'] = data[key][8]
        d1['fvideourl'] = data[key][9]
        d1['fperson'] = data[key][10]
        d1['flayerState'] = data[key][11]
        d1['fupddate'] = data[key][12]
        d1['foperator'] = data[key][13]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": layer.limit,
                 "page": layer.pageIndex,
                 "pages": (count + layer.limit - 1) // layer.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class LayerCreate(BaseModel):
    """楼层资料创建模型"""
    flayerNo: str = Field(..., description="楼层编号")
    flayerName: str = Field(..., description="楼层名称")
    flayerState: int = Field(..., description="楼层状态（1:启用，0:停用）")
    fsiteNo: str = Field(..., description="站点编号")
    fpointNo: str = Field(..., description="点位编号")

    # 坐标字段（支持数字或默认0）
    fpointL: float = Field(0, description="经度")
    fpointD: float = Field(0, description="纬度")
    fpointH: float = Field(0, description="高度")
    fpointW: float = Field(0, description="宽度")

    fvideoUrl: str = Field("", description="视频URL")
    fperson: Optional[str] = Field(None, description="负责人")  # 可选字段


@data_layer_router.post("/create", summary="新增楼层资料")
def create_data_layer(layer: LayerCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select flayer_no from a_ts_layer where flayer_no =  :flayerNo "
            data = db.execute(text(sql), {"flayerNo": layer.flayerNo}).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into a_ts_layer(flayer_no,flayer_name,fsite_no,fpoint_no,fpoint_l,fpoint_d,fpoint_h,fpoint_w,fvideo_url"
                if layer.fperson is not None:
                    sql = sql + ",fperson"
                sql = sql + ",flayer_state,fupddate,foperator)"
                sql = sql + " values(:flayerNo , :flayerName , :fsiteNo , :fpointNo , :fpointL , :fpointD , :fpointH , :fpointW , :fvideoUrl "
                if layer.fperson is not None:
                    sql = sql + ", :fperson "
                sql = sql + ", :flayerState ,now(), :userid );"
                db.execute(text(sql), {
                    "flayerNo": layer.flayerNo,
                    "flayerName": layer.flayerName,
                    "fsiteNo": layer.fsiteNo,
                    "fpointNo": layer.fpointNo,
                    "fpointL": layer.fpointL,
                    "fpointD": layer.fpointD,
                    "fpointH": layer.fpointH,
                    "fpointW": layer.fpointW,
                    "fvideoUrl": layer.fvideoUrl,
                    "fperson": layer.fperson,
                    "flayerState": layer.flayerState,
                    "userid": user_id,
                })
                # 返回
                d1 = {}
                d1['flayerNo'] = layer.flayerNo
                d1['flayerName'] = layer.flayerName
                d1['fsiteNo'] = layer.fsiteNo
                d1['fpointNo'] = layer.fpointNo
                d1['fpointL'] = layer.fpointL
                d1['fpointD'] = layer.fpointD
                d1['fpointH'] = layer.fpointH
                d1['fpointW'] = layer.fpointW
                d1['fimage'] = None
                d1['fvideourl'] = layer.fvideoUrl
                d1['fperson'] = layer.fperson
                d1['flayerState'] = layer.flayerState
                d1['fupddate'] = None
                d1['foperator'] = user_id
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_layer_router.post("/update", summary="楼层修改接口")
def update_data_layer(layer: LayerCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_ts_layer"
            sql = sql + " set flayer_name= :flayerName ,fperson= :fperson "
            sql = sql + ",fpoint_l= :fpointL ,fpoint_d= :fpointD ,fpoint_h= :fpointH ,fpoint_w= :fpointW "
            sql = sql + ",fvideo_url= :fvideoUrl ,flayer_state= :flayerState ,fupddate=now(),foperator= :userid "
            sql = sql + " where flayer_no= :flayerNo "
            db.execute(text(sql), {
                "flayerName": layer.flayerName,
                "fperson": layer.fperson,
                "fpointL": layer.fpointL,
                "fpointD": layer.fpointD,
                "fpointH": layer.fpointH,
                "fpointW": layer.fpointW,
                "fvideoUrl": layer.fvideoUrl,
                "flayerState": layer.flayerState,
                "userid": user_id,
                "flayerNo": layer.flayerNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_layer_router.post("/delete/{flayerNo}", summary="删除楼层信息")
def delete_data_layer(flayerNo: str = Path(description="楼层编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) f1 from a_ts_point where flayer_no= :flayerNo "
            data = db.execute(text(sql), {"flayerNo": flayerNo}).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from a_ts_layer where flayer_no= :flayerNo "
                db.execute(text(sql), {"flayerNo": flayerNo})
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
